package mumber

import org.apache.kafka.clients.producer.{KafkaProducer, ProducerRecord}
import org.apache.kafka.common.serialization.{StringDeserializer, StringSerializer}
import org.apache.spark.SparkConf
import org.apache.spark.streaming.kafka010.LocationStrategies.PreferConsistent
import org.apache.spark.streaming.kafka010.{ConsumerStrategies, KafkaUtils}
import org.apache.spark.streaming.{Durations, StreamingContext}

import java.sql.DriverManager
import scala.collection.mutable

object SexCount_spark_sql_liuyanyan {
  def main(args: Array[String]): Unit = {
    // 创建Spark配置
    val conf = new SparkConf().setAppName("CombinedSparkKafkaMySQL").setMaster("local[*]")
    // 创建StreamingContext，设置批处理间隔为2秒
    val ssc = new StreamingContext(conf, Durations.seconds(2))
    ssc.sparkContext.setLogLevel("WARN")

    // Kafka相关配置（用于消费）
    val kafkaParams = mutable.Map[String, Object]()
    kafkaParams += ("bootstrap.servers" -> "192.168.136.128:9092")
    kafkaParams += ("key.deserializer" -> classOf[StringDeserializer])
    kafkaParams += ("value.deserializer" -> classOf[StringDeserializer])
    kafkaParams += ("group.id" -> "combined-consumer-group")
    kafkaParams += ("auto.offset.reset" -> "earliest")

    // 要消费的Kafka主题
    val topics = Array("stuInfo")

    // 创建Kafka DStream
    val kafkaStream = KafkaUtils.createDirectStream[String, String](
      ssc,
      PreferConsistent,
      ConsumerStrategies.Subscribe[String, String](topics, kafkaParams)
    ).map(record => record.value())

    // 对读取到的数据进行处理，统计男女人数
    val genderCountStream = kafkaStream.flatMap(line => {
      val fields = line.split("\t")
      val gender = fields(2).toInt
      val genderStr = if (gender == 0) "female" else "male"
      Seq((genderStr, 1))
    }).reduceByKey((a, b) => a + b)

    // Kafka相关配置（用于生产）
    val kafkaParamsForProduce = new java.util.HashMap[String, Object]()
    kafkaParamsForProduce.put("bootstrap.servers", "192.168.136.128:9092")
    kafkaParamsForProduce.put("key.serializer", classOf[StringSerializer])
    kafkaParamsForProduce.put("value.serializer", classOf[StringSerializer])

    // 将统计结果发送到指定的Kafka主题
    genderCountStream.foreachRDD(rdd => {
      if (!rdd.isEmpty()) {
        rdd.foreach { case (gender, count) =>
          // 在每个执行节点上创建KafkaProducer对象
          val producer = new KafkaProducer[String, String](kafkaParamsForProduce)
          val record = new ProducerRecord[String, String]("stuSexcount", s"${gender}:${count}")
          producer.send(record)
          producer.close()
        }
      }
    })

    // 配置Kafka消费者参数，用于从新的stu读取数据
    val kakaParamsForMySQL = Map[String, Object](
      "bootstrap.servers" -> "192.168.136.128:9092",
      "key.deserializer" -> classOf[StringDeserializer],
      "value.deserializer" -> classOf[StringDeserializer],
      "group.id" -> "combined-niit",
      "enable.auto.commit" -> (false: java.lang.Boolean)
    )

    // 要读取的主题名称
    val topicNameForMySQL = Array("stuSexcount")

    // 从Kafka获取数据流
    val streamRddForMySQL = KafkaUtils.createDirectStream[String, String](
      ssc, PreferConsistent,
      ConsumerStrategies.Subscribe[String, String](topicNameForMySQL, kakaParamsForMySQL)
    )

    // 处理从Kafka获取的数据，将每行数据按照":"拆分并转换为(gender, count)类型的RDD
    val rowRddForMySQL = streamRddForMySQL.map(_.value()).map(line => {
      val fields = line.split(":")
      (fields(0), fields(1).toInt)
    })

    // 将统计结果推送到MySQL数据库
    rowRddForMySQL.foreachRDD(rdd => {
      rdd.foreachPartition(partition => {
        val connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/HUEL?characterEncoding=UTF-8&useSSL=false", "root", "123456")
        val statement = connection.createStatement()

        partition.foreach { case (gender, count) =>
          val sql = s"INSERT INTO test (gender, count) VALUES ('${gender}', ${count})"
          statement.executeUpdate(sql)
        }

        statement.close()
        connection.close()
      })
    })

    // 启动Spark Streaming应用
    ssc.start()
    ssc.awaitTermination()
  }
}
